{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 标书下载文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#下载文件\n",
    "import requests\n",
    "from lxml import etree\n",
    "import re,time\n",
    "import urllib\n",
    "from urllib  import request\n",
    "import os\n",
    "\n",
    "#计数变量\n",
    "c = 0\n",
    "f = 0\n",
    "g = 0\n",
    "try:\n",
    "    os.mkdir('./savefiles')\n",
    "    print('创建爬虫下载存储文件')\n",
    "except:\n",
    "    print('文件已经存在')\n",
    "\n",
    "page = 382\n",
    "#下载382页数据\n",
    "#http://ecp.sgcc.com.cn/ecp1.0/project_list.jsp?site=global&column_code=014001001&project_type=1&company_id=00&status=6&project_name=all&pageNo=2\n",
    "for i in range(2):\n",
    "    #抓取的所有页面的某一页\n",
    "    url ='http://ecp.sgcc.com.cn/ecp1.0/project_list.jsp?site=global&column_code=014001001&project_type=1&company_id=&status=&project_name=&pageNo=%d'%i\n",
    "    print(url)\n",
    "    #构造请求头\n",
    "    ss= 'Mozilla/5.0.html (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.html.2171.71 Safari/537.36'\n",
    "    headers = { \"User-Agent\": ss}\n",
    "    #第一次请求，开始请求某一页\n",
    "    response = requests.get(url=url,headers=headers)\n",
    "    #print(response.text）\n",
    "    #返回的数据做成xpath对象，便于下一步xpath匹配\n",
    "    xpathtree = etree.HTML(response.text)\n",
    "    #匹某一标书的标签内容，共多少标书\n",
    "    res = xpathtree.xpath('//tr[@align=\"left\"]')\n",
    "    #获取\n",
    "    a= 0\n",
    "    b= 0\n",
    "    del res[0]\n",
    "    #每一个标书页面开始循环\n",
    "    for resi in res:        \n",
    "        #打印标书的状态\n",
    "        print(resi.xpath('./td[@class=\"black40\"][1]/text()'))\n",
    "        #打印标书的时间\n",
    "        print(resi.xpath('./td[@class=\"black40\"][4]/text()'))\n",
    "        #获取标书的可以下载页面的详情url\n",
    "        str1 = resi.xpath('./td[@class=\"black40\"]/a/@onclick')\n",
    "        num = re.search('showProjectDetail\\((.*?),\\'(.*?)\\'\\)',str1[0])\n",
    "        \n",
    "        num = num.group(2)\n",
    "        url2  = 'http://ecp.sgcc.com.cn/ecp1.0/html/project/014001001/%s.html'%num\n",
    "        #第二次请求，最终下载文件页面的地址\n",
    "        response2 = requests.get(url=url2, headers=headers)\n",
    "        original= 'http://ecp.sgcc.com.cn'\n",
    "        #可以下载的页面，检测下载地址是不是可以下载\n",
    "        if len(original+etree.HTML(response2.text).xpath('//td/a/@href')[0])>45:\n",
    "            #如果下载地址的字符超过45，表示是可以尝试下载的地址\n",
    "            #标书的状态\n",
    "            projectstatus = etree.HTML(response2.text).xpath('//td[2]/text()')[0]\n",
    "            #只下载招标结束的文件         \n",
    "            #匹配出标书的状态\n",
    "            if re.search('(\\S+.*?)', projectstatus):\n",
    "                #获取标书的状态\n",
    "                rename = re.search('(\\S+.*?)', projectstatus).group(1)         \n",
    "                #匹配标书的名字\n",
    "                projectname =   etree.HTML(response2.text).xpath('//td[2]/text()')[2]\n",
    "                #拼接标书的名字\n",
    "                projectname= rename+projectname\n",
    "                #第三次请求，获取标书的最终下载地址\n",
    "                downloadfileurl = original+etree.HTML(response2.text).xpath('//td/a/@href')[0]\n",
    "                try:\n",
    "                    urllib.request.urlretrieve(url=downloadfileurl,filename='./savefiles/%s.zip'%projectname)\n",
    "                    # 下载成功一次标书，b增加1，统计本页标书下载成功的数量\n",
    "                    b+=1\n",
    "                    f+=1\n",
    "                    #time.sleep(2)\n",
    "                except:\n",
    "                    g+=1\n",
    "                    print('链接超时下载失败数g',g)\n",
    "                    continue\n",
    "            # else:\n",
    "            #     print('非招标结束状态')\n",
    "        else:\n",
    "            print('此标的文件无法下载',url2)\n",
    "        a+=1\n",
    "    \n",
    "    c+=1\n",
    "    print('本页面共%d项标书'%a)\n",
    "    print('本页成功下载%d件标书'%b)\n",
    "    print('第%d页下载完成'%c)\n",
    "    \n",
    "print('最终下载合格标书%d项目'%f)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 解压文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "查看所有文件数量 0\n"
     ]
    }
   ],
   "source": [
    "#解压文件\n",
    "import os,re\n",
    "import shutil\n",
    "\n",
    "import zipfile\n",
    "lis0  = os.listdir('./savefiles')\n",
    "lis1  = []\n",
    "for f in lis0:\n",
    "    if not  f.startswith('.'):\n",
    "        lis1.append(f)\n",
    "#读取savefiles文件夹内的所有的文件\n",
    "#去掉.开头的隐藏文件\n",
    "print('查看所有文件数量',len(lis1))\n",
    "try:\n",
    "    os.mkdir('./unpacked')\n",
    "except:\n",
    "    shutil.rmtree('./unpacked') \n",
    "    os.mkdir('./unpacked')\n",
    "    print('原文件已经删除，并继续创建')\n",
    "#遍历所有文件\n",
    "h = 0\n",
    "k = 0\n",
    "for i in lis1:\n",
    "    try:\n",
    "        #匹配获取文件的名字\n",
    "        name = re.search('(.*?).zip',i)\n",
    "        #拿到文件名字\n",
    "        name = name.group(1)\n",
    "        #文件名进行与路径的拼接\n",
    "        # print(os.path.join('./test3',i))\n",
    "        i = os.path.join('./savefiles',i)\n",
    "\n",
    "        print('i',i)\n",
    "        #解压文件\n",
    "        zip_file_contents = zipfile.ZipFile(i, 'r')\n",
    "      \n",
    "    except:\n",
    "        k+=1\n",
    "\n",
    "        continue\n",
    "    #遍历要解压文件内的所有数据\n",
    "    for file in zip_file_contents.namelist():\n",
    "         try:\n",
    "             #进行编码，这块头大，编码问题\n",
    "             filename = file.encode('cp437').decode('gbk')\n",
    "             #先使用cp437编码，然后再使用gbk解码\n",
    "             #开始解压                              \n",
    "             zip_file_contents.extract(file,'./unpacked/%s'%name)\n",
    "             #创建名字\n",
    "             file ='./unpacked/{}/{}'.format(name,file)\n",
    "             filename = './unpacked/{}/{}'.format(name,filename)\n",
    "         except:\n",
    "            \n",
    "            continue\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 写入t x t  状态"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import os, re\n",
    "import zipfile\n",
    "import pandas as pd\n",
    "# 显示所有行列\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', None)\n",
    "\n",
    "#把所有标书的状态全部存储进一个txt\n",
    "testlist = os.listdir('./unpacked')\n",
    "for i in  testlist:\n",
    "    #匹配所有文件的前面的4个字符\n",
    "    if re.match('(\\w){4}',i):  \n",
    "        stauts = re.match('(\\w){4}',i).group()\n",
    "        #写进t x t\n",
    "        fileswrite = open('./unpacked/%s/status.txt'%i,'w')\n",
    "        fileswrite.write(stauts)\n",
    "        fileswrite.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 货物清单数据表合并，添加新状态，去除重复数据，存储数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import os, re\n",
    "import zipfile\n",
    "import pandas as pd\n",
    "# 显示所有行列\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', None)\n",
    "\n",
    "lis3 = os.listdir('./unpacked')\n",
    "lis4 = []\n",
    "\n",
    "for f in lis3:\n",
    "    if not f.startswith('.'):\n",
    "        f = os.path.join('./unpacked', f)\n",
    "        lis4.append(f)\n",
    "#去掉.开头的隐藏文件\n",
    "k = 0\n",
    "\n",
    "# 存储sheetname的列表\n",
    "listsheet = []\n",
    "#遍历所有文件\n",
    "for i in lis4:\n",
    "    \n",
    "    for j in os.listdir(i):\n",
    "        #列出每个文件内的具体内容\n",
    "        if re.search('[货物清单].*?.xls', j):\n",
    "            #匹配有没有货物清单的excel表\n",
    "            final = os.path.join(i, j)\n",
    "            #如果有就进行拼接路径\n",
    "            #print('能检测到货物清单的文件夹',i)\n",
    "            #并且获取文件的投标状态\n",
    "            statusread = open('%s/status.txt'%i,'r')\n",
    "            status1 = statusread.read()\n",
    "            #打印路径和状态查看\n",
    "            print(status1)\n",
    "            print(final)\n",
    "            #读取货物清单的excel\n",
    "            excel_reader = pd.ExcelFile(final) \n",
    "            #每个excel 有多个 sheet\n",
    "            sheet_names = excel_reader.sheet_names      \n",
    "            print('这个excelsheet数',len(sheet_names))\n",
    "            #列出所有sheet的名字\n",
    "            k+=1\n",
    "            for i in range(len(sheet_names)):\n",
    "                #读取每个sheet\n",
    "                df1 = excel_reader.parse(sheet_name=sheet_names[i],skiprows=1)\n",
    "                #skiprows=1的意思是跳过excel的第一行，因为第一行并不是我们的表头，表格有毛病\n",
    "                df1['状态'] = status1\n",
    "                #读取出来的数据，df1创建一个新的字段‘状态’\n",
    "                listsheet.append(df1)\n",
    "                \n",
    "print('k个货物清单完成读取',k)   \n",
    "                #把所有的读取出来的数据的名字存储进 listsheet\n",
    "#使用concat 把所有的表进行垂直拼接\n",
    "df2 = pd.concat(listsheet)\n",
    "#拼接出来的df2 列数很多，我们只要重要的14列，与1列我们添加的 状态列\n",
    "headname = ['包号','网省采购申请行号','项目单位', '需求单位','项目名称','工程电压等级','物资名称', '物资描述', '单位', '数量', '交货日期', '交货地点', '备注', '技术规范ID', '状态']\n",
    "#取出想要的列\n",
    "df3 = df2[headname]\n",
    "#转成csv 保存\n",
    "df3.to_csv('test3.csv')\n",
    "df3.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "headname = pd.read_csv('test.csv')\n",
    "headname= list(headname)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
